﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Student_management.Sql
{
    class HocSinhsql
    {
        Connection db = new Connection();
        public DataTable getHS(string maHS)
        {
            return db.getDS("Select * from HocSinh where MaHS = '" + maHS + "'");
        }

        public DataTable getHS()
        {
            return db.getDS("Select * from HocSinh");
        }

        public bool check(string makh)
        {
            if (db.getDS("Select * from KhachHang where MaKH = '" + makh + "'").Rows.Count > 0)
                return true;
            return false;
        }
        public bool Xoa(string mahs)
        {
            string[] param = { "@MaHS" };
            object[] value = { mahs };
            string query = "Delete from HocSinh where MaHS=@MaHS";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public bool Sua(HocSinh hs)
        {
            string[] param ={"@MaHS","@HoTen","@NgaySinh","@GioiTinh","@DiaChi","@MaLop","@Email","@SDT"};
            object[] value = { hs.MaHS,hs.TenHS, hs.NgaySinh, hs.GioiTinh, hs.DiaChi, hs.MaLop, hs.Email, hs.SDT};
            string query = "Update HocSinh"
                           + " set HoTen=N''+@HoTen,NgaySinh=@NgaySinh,GioiTinh=@GioiTinh,DiaChi=N''+@DiaChi,"
                           + "MaLop=@MaLop,Email=@Email, SDT=@SDT"
                           + " where MaHS=@MaHS";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public bool Them(HocSinh hs)
        {
            string[] param ={"@MaHS","@HoTen","@NgaySinh","@GioiTinh","@DiaChi","@MaLop","@Email","@SDT"};
            object[] value = { hs.MaHS, hs.TenHS, hs.NgaySinh, hs.GioiTinh, hs.DiaChi, hs.MaLop, hs.Email, hs.SDT};
            string query = "Insert into HocSinh"
                           + "(MaHS,HoTen,NgaySinh,GioiTinh,DiaChi,MaLop,Email,SDT)"
                           + " values (@MaHS,N''+@HoTen,@NgaySinh,@GioiTinh,N''+@DiaChi,@MaLop,@Email,@SDT)";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public string SearchDaTaGrid()
        {
            int Count = 0;
            string MaHS = "";
            DataTable dt = new DataTable();//tao bang tam de luu
            dt = db.getDS("Select MaHS From HocSinh");
            for (int i = 1; true; i++)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    if (dt.Rows[j]["MaHS"].ToString() != "HS" + i)
                    {
                        Count++;//dem so lan khac
                    }
                    else
                    {
                        Count = 0;
                        break;
                    }
                }
                if (Count == dt.Rows.Count)// new so lan khac bang so hang cua bang, nghia la khong co dong nao trung thi tu dong add
                {
                    MaHS = "HS" + i;
                    break;
                }
            }
            return MaHS;
        }
    }
}
